create database Marvell_Consultancies_Ltd_eproject
go
use Marvell_Consultancies_Ltd_eproject
go
create table Company
(
CompanyID int primary key not null identity(1,1),
Name nvarchar(100) not null,
Address nvarchar (150) not null,
PhoneNumber nvarchar (30) not null,
Introduction nvarchar (300) not null,
Website nvarchar(100),
fax nvarchar (100)
)
go
create table Payment
(
PaymentID int primary key not null identity(1,1),
CompanyID int foreign key references Company not null,
Paydate datetime not null,
Salary Money,
Quantity int,
Discount float,
Status bit not null
)
go

create table Classification
(
ClassificationID int primary key not null identity(1,1),
Name nvarchar (100) not null,

)
go
create table Jobseeker
(
JobseekerID int primary key not null identity(1,1),
Name nvarchar (100) not null,
ClassificationID int foreign key references Classification not null,
DateOfBirth datetime not null,
Sex bit not null,
Address nvarchar(150) not null,
Location nvarchar(150) not null,
PhoneNumber nvarchar (30) not null,
Email nvarchar (50),
Salary money ,
Skill nvarchar (150) not null,
Experience nvarchar (150),
Education nvarchar (150),
[Certificate] nvarchar (150),
[Image] nvarchar (100),
Note nvarchar (100)
)
go

create table Vacancies
(
VacanciesID int primary key not null identity(1,1),
ClassificationID int foreign key references Classification not null,
CompanyID int foreign key references Company not null,
Position nvarchar (100) not null,
Quantity int not null,
Salary money not null,
Skill nvarchar (150),
Experience nvarchar (200),
Education nvarchar (150),
[Certificate] nvarchar (150),
Location nvarchar (100),
DatePost datetime not null,
DeadLine datetime not null,
Status bit
)

go

create table Interview
(
InterviewID int primary key not null identity (1,1),
JobseekerID int foreign key references Jobseeker not null,
VacanciesID int foreign key references Vacancies not null,
DateOfInterview datetime,
Status bit,
Result bit,
Note nvarchar (100)
)
go


---------creare views of Classisfication and Jobseeker
go

create view View_Classification
as
select ClassificationID,Name,(select count(*) from Jobseeker where ClassificationID = Classification.ClassificationID)as
countJobseeker , (select count(*) from Vacancies where ClassificationID=Classification.ClassificationID) as
countVacancies
from Classification

go

create view View_Jobseeker
as
select JobseekerID,Name, (Select Name from Classification where ClassificationID = Jobseeker.ClassificationID) as speciality, Sex from Jobseeker

go
---------Example Records

insert into  Company values ('ngan hang','ha long','123','fsladkfjl','www.nganhang.com','132-433-23')
insert into  Company values ('benh vien','ha noi','345','sdfasdf','www.benhvien.com','35435345')
insert into  Company values ('truong hoc','thanh hoa','456','asdfdsdsf','www.truonghoc.com','435-34-5')
insert into  Company values ('cong an','son la','567','ddsagfgdsaf','www.congan.com','43-53-45')
insert into  Company values ('CAGT','hai phong','789','asdffghdf','www.CAGT.com','4352-3-45')
insert into  Company values ('cuu hoa','hue','543','sdfdgfhdf','www.cuuhoa.com','34-543-5')

---------create procedure of Company
go

create proc pcGetAllCompany
as	
	select CompanyID,Name,Address,PhoneNumber,Introduction,Website,fax from Company order by CompanyID DESC

go

create proc pcGetTopCompany
as
	select top(10) CompanyID,Name,Address,PhoneNumber,Introduction,Website,fax from Company order by CompanyID DESC

go

create proc pcGetTop1CompanyID
as
	select top(1) CompanyID from Company order by CompanyID DESC

go


create proc pcGetAllName
as
	select Name from Company


go

create proc pcInsertCompany
@Name as nvarchar(100),
@Address as nvarchar (150),
@PhoneNumber as nvarchar (30),
@Introduction as nvarchar (300),
@Website as nvarchar (100),
@fax as nvarchar (100)
as
	insert into Company values (@Name,@Address,@PhoneNumber,@Introduction,@Website,@fax);

go

create proc pcUpdateCompany
@CompanyID as int,
@Name as nvarchar(100),
@Address as nvarchar (150),
@PhoneNumber as nvarchar (30),
@Introduction as nvarchar (300),
@Website as nvarchar (100),
@fax as nvarchar (100)
as
	update Company set 
	Name = @Name,
	Address = @Address,
	PhoneNumber = @PhoneNumber,
	Introduction = @Introduction,
	Website = @Website,
	fax = @fax
	where CompanyID = @CompanyID

go

create proc pcDeleteCompany
@CompanyID as int
as
	delete from Company where CompanyID=@CompanyID

exec pcGetTopCompany

go
--create procedures of job seeker

create proc pcInsertJobseeker
@Name as nvarchar (50),
@ClassificationID as int,
@DateOfBirth as datetime,
@Sex as bit,
@Address as nvarchar (150),
@Location as nvarchar (150),
@PhoneNumber as nvarchar (30),
@Email as nvarchar(50),
@Salary as money,
@Skill as nvarchar(150),
@Experience as nvarchar(150),
@Education as nvarchar(150),
@Certificate as nvarchar(150),
@Note as nvarchar(100)
as
	insert into Jobseeker values (@Name,@ClassificationID,@DateOfBirth,@Sex,@Address,@Location,@PhoneNumber
		,@Email,@Salary,@Skill,@Experience,@Education,@Certificate,'Image',@Note)

go


create proc pcUpdateJobseeker
@JobseekerID as int,
@Name as nvarchar (50),
@ClassificationID as int,
@DateOfBirth as datetime,
@Sex as bit,
@Address as nvarchar (150),
@Location as nvarchar (150),
@PhoneNumber as nvarchar (30),
@Email as nvarchar(50),
@Salary as money,
@Skill as nvarchar(150),
@Experience as nvarchar(150),
@Education as nvarchar(150),
@Certificate as nvarchar(150),
@Note as nvarchar(100)
as	
	update Jobseeker set		
	Name =@Name,
	ClassificationID =@ClassificationID,
	DateOfBirth =@DateOfBirth,
	Sex =@Sex,
	Address =@Address,
	Location =@Location,
	PhoneNumber =@PhoneNumber,
	Email =@Email,
	Salary =@Salary,
	Skill =@Skill,
	Experience=@Experience ,
	Education =@Education,
	[Certificate] =@Certificate,
	[Image]= 'Image',
	Note =@Note
where JobseekerID = @JobseekerID

go

create proc pcGetAllJobseeker
as
	select JobseekerID,Name,ClassificationID,DateOfBirth,Sex,
	Address,Location,PhoneNumber,Email,Salary,Skill,Experience,Education,[Certificate],[Image],Note from Jobseeker order by JobseekerID DESC
	
go

create proc pcDeleteJobseeker
@JobseekerID as int
as
	delete from Jobseeker where JobseekerID = @JobseekerID

go

create proc pcGetTopJobseeker
as
	select top(10) JobseekerID,Name,ClassificationID,DateOfBirth,Sex,
	Address,Location,PhoneNumber,Email,Salary,Skill,Experience,Education,[Certificate],[Image],Note from Jobseeker order by JobseekerID DESC

go

create proc prGetAllView_Jobseeker
as
	select top(10) * from View_Jobseeker order by JobseekerID DESC

GO

--------------create procedure of classification 

create proc pcInsertClassification
@Name as nvarchar (100)
as
	insert into Classification values (@Name)

go

create proc pcUpdateClassification
@ClassificationID as int,
@Name as nvarchar (100)
as
	update Classification set
	Name = @Name
	where
	ClassificationID =@ClassificationID

go

create proc pcDeleteClassification
@ClassificationID as int
as
	delete from Classification
	where ClassificationID = @ClassificationID

go

create proc pcGetAllView_Classification
as
	select * from View_Classification

go

----------------create procedure of vacancies
create proc pcInsertVacancies
@ClassificationID as int,
@CompanyID as int ,
@Position as nvarchar (100) ,
@Quantity as int ,
@Salary as money ,
@Skill as nvarchar (150),
@Experience as nvarchar (200),
@Education as nvarchar (150),
@Certificate as nvarchar (150),
@Location as nvarchar (100),
@DatePost as datetime ,
@DeadLine as datetime ,
@Status as bit
as
	insert into Vacancies values
	(@ClassificationID,@CompanyID,@Position,@Quantity,@Salary,@Skill,@Experience,@Education,@Certificate,@Location,@DatePost,@DeadLine,@Status)
	
go
 
create proc pcUpdateVacancies
@VacanciesID as int,
@ClassificationID as int,
@CompanyID as int ,
@Position as nvarchar (100) ,
@Quantity as int ,
@Salary as money ,
@Skill as nvarchar (150),
@Experience as nvarchar (200),
@Education as nvarchar (150),
@Certificate as nvarchar (150),
@Location as nvarchar (100),
@DatePost as datetime ,
@DeadLine as datetime ,
@Status as bit
as 
	update Vacancies set
	ClassificationID= @ClassificationID ,
	CompanyID= @CompanyID ,
	Position=@Position ,
	Quantity=@Quantity ,
	Salary=@Salary ,
	Skill=@Skill ,
	Experience=@Experience ,
	Education=@Education ,
	[Certificate]=@Certificate ,
	Location=@Location ,
	DatePost=@DatePost ,
	DeadLine=@DeadLine ,
	Status=@Status 
	where VacanciesID =@VacanciesID
go
	
create proc pcDeleteVacancies
@VacanciesID as int	
as	
	delete from Vacancies
	where VacanciesID = @VacanciesID

go
	
create proc pcGetAllVacancies
as
	select VacanciesID,ClassificationID,CompanyID,Position,Quantity,Salary,
	Skill,Experience,Education,[Certificate],Location,DatePost,DeadLine,Status from Vacancies

go

--------------create procedures of interview

create proc pcInsertInterview
@JobseekerID as int ,
@VacanciesID as int ,
@DateOfInterview as datetime,
@Status as bit,
@Result as bit,
@Note as nvarchar (100)
as
	insert into Interview values(@JobseekerID,@VacanciesID,@DateOfInterview,
	@Status,@Result,@Note)

go

create proc prUpdateInterview
@InterviewID as int,
@JobseekerID as int ,
@VacanciesID as int ,
@DateOfInterview as datetime,
@Status as bit,
@Result as bit,
@Note as nvarchar (100)
as
	update Interview set	
	JobseekerID = @JobseekerID ,
	VacanciesID = @VacanciesID ,
	DateOfInterview = @DateOfInterview,
	Status = @Status,
	Result = @Result,
	Note = @Note
	where InterviewID = @InterviewID

go

create proc prDeleteInterview
@InterviewID as int
as
	delete from Interview where InterviewID = @InterviewID

go

create proc pcGetAllInterview
as
select * from Interview order by InterviewID DESC

go

-----------------create procedures of payment
create proc pcGetAllPayment
as
	select * from Payment

go

create proc pcUpdatePayment
@PaymentID as int ,
@Paydate as datetime ,
@Discount as float,
@Status as bit
as
	update Payment set
	Paydate =@Status,
	Discount =@Status,
	Status =@Status
	where
	PaymentID = @PaymentID

go

-------------------create triggers



CREATE TRIGGER trgVacaniesInsert
ON Vacancies
FOR INSERT
AS

DECLARE @Code as INT
SELECT @code = CompanyID
FROM inserted


DECLARE @Salary as MONEY
SELECT @Salary = Salary
FROM inserted


BEGIN
INSERT INTO Payment( CompanyID, Salary) VALUES (@code, @Salary)
END
GO

/****************************/
GO
CREATE TRIGGER trgVacaniesDelete
ON Vacancies
FOR DELETE
AS

DECLARE @Code as INT
SELECT @code = VacanciesID
FROM deleted



BEGIN
DELETE FROM Payment WHERE PaymentID = @Code
END
GO


/****************************/

CREATE TRIGGER trgInterviewUpdate
ON Interview
FOR UPDATE
AS

DECLARE @Status as BIT
SELECT @Status = Status
FROM inserted


DECLARE @VacanciesID as INT
SELECT @VacanciesID = VacanciesID
FROM inserted


DECLARE @PaymentID as INT
SELECT @PaymentID = (Select VacanciesID  From Vacancies  where @VacanciesID =  [Vacancies].VacanciesID )

IF @Status = 1
BEGIN
UPDATE Payment SET
Quantity = Quantity +1
Where @PaymentID = PaymentID
END

IF @Status = 0
BEGIN
UPDATE Payment SET
Quantity = Quantity -1
Where @PaymentID = PaymentID
END
GO

/*******************************/
CREATE TRIGGER trgPaymentUpdate
ON Payment
FOR UPDATE
AS

DECLARE @Quantity AS INT
SELECT @Quantity = Quantity
FROM Inserted

DECLARE @PaymentID AS INT
SELECT @PaymentID = PaymentID
FROM Inserted

IF @Quantity > 0 AND @Quantity <=30
BEGIN
UPDATE Payment SET
[Discount] = 5
WHERE PaymentID = @PaymentID
END


IF @Quantity > 30 AND @Quantity <=100
BEGIN
UPDATE Payment SET
[Discount] = 8
WHERE PaymentID = @PaymentID
END

IF @Quantity > 100 AND @Quantity <=250
BEGIN
UPDATE Payment SET
[Discount] = 10
WHERE PaymentID = @PaymentID
END

IF @Quantity > 250 AND @Quantity <=500
BEGIN
UPDATE Payment SET
[Discount] = 15
WHERE PaymentID = @PaymentID
END

IF @Quantity > 500
BEGIN
UPDATE Payment SET
[Discount] = 20
WHERE PaymentID = @PaymentID
END

GO











